package com.syni.mdd.yhd.common.all.dao.jpa;

import com.syni.mdd.yhd.common.all.dao.BaseJPADao;
import com.syni.mdd.yhd.common.all.entity.BmsDxOrderStatus;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BmsDxOrderStatusDao extends BaseJPADao<BmsDxOrderStatus,Integer> {
    BmsDxOrderStatus findByOrderIdAndStatus(Integer orderId,Integer status);

    List<BmsDxOrderStatus> findByOrderIdAndStatusIn(Integer orderId,Set status);

    List<BmsDxOrderStatus> findByOrderId(Integer orderId);

    List<BmsDxOrderStatus> findByUserIdAndStatus(Integer userId,Integer status);

    List<BmsDxOrderStatus> findByOrderTypeAndStatus(Integer orderType,Integer status);

    List<BmsDxOrderStatus> findByOrderTypeInAndStatus(Set orderType,Integer status);

    /** 查找用户的全部订单 **/
    @Query(value = "select * from bms_dx_order_status bdos where bdos.user_id = ?1 " +
            "group by bdos.order_id",nativeQuery = true)
    List<BmsDxOrderStatus> selfFindAllOrder(Integer userId,Pageable pageable);

    /** 查找用户的部分状态订单 **/
    @Query(value = "select * from bms_dx_order_status bdos where bdos.user_id = ?1 and " +
            "bdos.status = ?2",nativeQuery = true)
    List<BmsDxOrderStatus> selfFindStausOrder(Integer userId,Integer status,Pageable pageable);

    /** 查找店铺的消费人數 **/
    @Query(value = "select count(distinct bdos.user_id) from bms_dx_order_status bdos where bdos.business_id = ?1 and " +
            "bdos.status in (1,2,4) and bdos.is_delete = 0",nativeQuery = true)
    Integer countBusinessConsumerUsersNum(Integer businessId);

    /** 查找店铺的消费人列表(按消费次数进行排序) **/
    @Query(value = "select count(id) num,user_id userId,sum(actual_consum) total from (" +
            "select bdos.id,bdos.user_id,bdo.actual_consum from bms_dx_order_status bdos LEFT JOIN bms_dx_order bdo on bdos.order_id = bdo.id " +
            "where bdos.business_id = ?1 and bdos.order_type in(1,2,3) and bdos.status in (1,2,4) and " +
            "bdos.is_delete = 0 group by bdos.order_id) ntable group by user_id order by num desc,total desc",nativeQuery = true)
    List<Map> findBusinessConsumerUsers(Integer businessId, Pageable pageable);

    /** 查找店铺的消费人列表(按消费次数进行排序) [带userid]**/
    @Query(value = "select count(id) num,user_id userId,sum(actual_consum) total from (" +
            "select bdos.id,bdos.user_id,bdo.actual_consum from bms_dx_order_status bdos LEFT JOIN bms_dx_order bdo on bdos.order_id = bdo.id " +
            "where bdos.business_id = ?1 and bdos.order_type in(1,2,3) and bdos.status in (1,2,4) and " +
            "bdos.is_delete = 0 group by bdos.order_id) ntable where user_id in (?2) group by user_id order by num desc,total desc",nativeQuery = true)
    List<Map> findBusinessConsumerUsers(Integer businessId, Set userIds);

    /** 查找店铺的消费人列表(按消费次数进行排序) [不带userid]**/
    @Query(value = "select count(id) num,user_id userId,sum(actual_consum) total from (" +
            "select bdos.id,bdos.user_id,bdo.actual_consum from bms_dx_order_status bdos LEFT JOIN bms_dx_order bdo on bdos.order_id = bdo.id " +
            "where bdos.business_id = ?1 and bdos.status in (1,2,4) and " +
            "bdos.is_delete = 0 group by bdos.order_id) ntable group by user_id order by num desc",nativeQuery = true)
    List<Map> findBusinessConsumerUsers(Integer businessId);

    /** 查找消费人列表 [通过用户集]**/
    @Query(value = "select count(id) num,user_id userId,sum(actual_consum) total from (" +
            "select bdos.id,bdos.user_id,bdo.actual_consum from bms_dx_order_status bdos LEFT JOIN bms_dx_order bdo on bdos.order_id = bdo.id " +
            "where bdos.user_id in (?1) and bdos.status in (1,2,4) and " +
            "bdos.is_delete = 0 group by bdos.order_id) ntable group by user_id",nativeQuery = true)
    List<Map> findBusinessConsumerUsers(Set userIds);

    /**统计活动收入的钱（买单订单）**/
    @Query(value = "select IFNULL(sum(bdo.actual_consum),0) total,IFNULL(sum(bdo.discount_consum),0) discountConsum,bdo.bms_business_id businessId from bms_dx_order_status bdos " +
            "LEFT JOIN bms_dx_order bdo on bdos.order_id = bdo.id " +
            "where bdo.full_coupon_id in (?1) and bdos.status in (1) and " +
            "bdo.order_type in(1,2) group by bdo.bms_business_id",nativeQuery = true)
    List<Map<String,Object>> countActivityOrderReceCoupon(Set userReceIds);


    /** 查找店铺的消费人列表(按消费次数进行排序) [带userid,只针对买单订单]**/
    @Query(value = "select count(id) num,user_id userId,sum(actual_consum) total from (" +
            "select bdos.id,bdos.user_id,bdo.actual_consum from bms_dx_order_status bdos LEFT JOIN bms_dx_order bdo on bdos.order_id = bdo.id " +
            "where bdos.business_id = ?1 and bdos.status in (1) and bdos.order_type in(1,2) and " +
            "user_id in (?2) group by bdos.order_id) ntable group by user_id order by num desc,total desc",nativeQuery = true)
    List<Map> countCouponOrderSuccessNum(Integer businessId,Set<Integer> userIds);

    /** 查找店铺的消费人列表(按消费次数进行排序) [带userid,只针对团购订单]**/
    @Query(value = "select count(order_id) num,user_id userId,sum(gac) total from (select order_id,user_id,sum(gbuy_actual_consum) gac from bms_dx_group_buy_use bdgu LEFT JOIN bms_dx_order bdo on bdgu.order_id = bdo.id \n" +
            "where bdo.bms_business_id = ?1 and bdgu.status in (1) and user_id in(?2) " +
            "group by use_time) ntab group by user_id",nativeQuery = true)
    List<Map> countGroupOrderSuccessNum(Integer businessId, Set<Integer> userIds);

    @Query(value = "select count(id) num,status from bms_dx_order_status bdos where bdos.order_type in(?1) and " +
            "bdos.user_id = ?2 and bdos.status in(0,2,6) group by status",nativeQuery = true)
    List<Map<String, Object>> countNoPayAndUse(Set<Integer> orderTypes, Integer userId);
}
